#!/usr/bin/perl
#########################################
# NAME createdb.pl
# DESC: creates the mySQL 'brain' database structure
#       required by the brain application.
#       note: it assumes that MYSQL has been set up to allow the user
#       'brain' to access the database 'brain' with the password 'brainpasswd'
#       For this to work the administrator has to have created an empty
#       database called 'brain' that this user has permission to use.  For example:
#          use mysql;
#          insert into
#              user (host, user, password)
#              values('localhost','brain',password('brainpasswd'));
# 
#          insert into
#             -> db (host,db,user,Select_priv, Insert_priv, Update_priv, 
#                                        Delete_priv, Create_priv, Drop_priv)
#             ->  values ('localhost','brain','brain',
#                                        'Y','Y','Y','Y','Y','Y');
#
#          mysqladmin -u root -p create brain;
#          mysqladmin -u root -p reload
#
# HIST: 13jan2008  GJ  ORIGINAL VERSION
#

use DBI;
use brainDB;


my ($dbh) = brainDB->new();

my $alltables = $dbh->{dbmeta}->table_list();
my $realtablelist = [];

if (scalar @ARGV > 0)
{
    $tablelist = \@ARGV;
    foreach my $table (@{$tablelist})
    {
        print "Processing options for $table\n";
        if ($table =~ m/%/)
        {
            $table =~ s/%//g;
            push @{$realtablelist},grep /^$table/,@{$alltables};
        }
        else
        {
            push @{$realtablelist},$table;
        }
    }
}
else
{
    $realtablelist = $alltables;
}

create_tables($realtablelist);

sub create_tables
{
    my ($tables) = @_;
    foreach my $table (sort @{$tables})
    {
        my $tablespec = $dbh->{dbmeta}->table($table);
        my $query = "create table $table ";
        my @fieldspec;
        foreach my $fieldkey (@{$tablespec->{fields}})
        {
            my $fielddef;
            $fielddef = sprintf("%s %s",$fieldkey->{field},$fieldkey->{type});
            $fielddef .= "($fieldkey->{size})" if (exists($fieldkey->{size}));
            $fielddef .= " $fieldkey->{opt}" if (exists($fieldkey->{opt}));
            push(@fieldspec,$fielddef);
        }
        $query .= sprintf("(%s)",join(',',@fieldspec));
        print "Query: $query\n";
        print "Creating table $table...";
        $result = $dbh->do($query);
        print "Error: ",$dbh->{dbh}->errstr(),"\n" unless($result);
        print "Done\n";
    }
}










######################################################
#$dbh = DBI->connect("DBI:mysql:brain","brain","brainpasswd");
#
#$dbh->do('insert into table users (fname,sname,email) values(\'test\',\'test\',\'test\')');
#
#$qry = $dbh->prepare('describe users');
#$qry->execute();
#
#while (my $row = $qry->fetchrow_hashref()) {
#   print $row->{Field}."  ";
#   print $row->{Type}."\n";
#}
#
#$qry->finish();
#$dbh->disconnect();
#
#print "</body>\n";
#print "</html>\n";
